"""
Case Type   : fullsql
Case Name   : 普通用户无权限查询系统表,系统视图,系统函数
Create At   : 2022/01/29
Owner       : zhao-pengcheng-g
Description :
    1.创建普通用户
    2.普通用户查询系统表
    3.普通用户查询系统视图
    4.普通用户查询dbe_perf.get_global_full_sql_by_timestamp函数
    5.普通用户查询dbe_perf.get_global_slow_sql_by_timestamp函数
    6.普通用户查询解析函数
    7.环境清理
Expect      :
    1.创建成功
    2.失败，合理报错，提示permission denied
    3.失败，合理报错，提示permission denied
    4.失败，合理报错，提示permission denied
    5.失败，合理报错，提示permission denied
    6.失败，合理报错，提示permission denied
    7.环境清理成功
History     :
"""

import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0012 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.user_name = 'fullsql_user_0012'

    def test_fullsql(self):
        text = '---step1:创建普通用户,expect:成功---'
        self.logger.info(text)
        create_cmd = f'''drop user if exists {self.user_name};
                         create user {self.user_name} \
                         with password '{macro.COMMON_PASSWD}';'''
        create_msg = self.sh_primy.execut_db_sql(create_cmd)
        self.logger.info(create_msg)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG and
                      self.constant.CREATE_ROLE_SUCCESS_MSG,
                      create_msg, '执行失败:' + text)

        text = '---step2:普通用户查询系统表,expect:失败，' \
               '合理报错，提示permission denied---'
        self.logger.info(text)
        sql_cmd_2 = f'''set role {self.user_name} password \
                         '{macro.COMMON_PASSWD}';
                          select * from statement_history limit 1;'''
        sql_msg_2 = self.sh_primy.execut_db_sql(sql_cmd_2, dbname='postgres')
        self.logger.info(sql_msg_2)
        self.assertIn('permission denied', sql_msg_2, '执行失败:' + text)

        text = '---step3:普通用户查询系统视图,expect:失败，' \
               '合理报错，提示permission denied---'
        self.logger.info(text)
        sql_cmd_3 = f'''set role {self.user_name} password \
                                 '{macro.COMMON_PASSWD}';
                        select * from dbe_perf.statement_history limit 1;'''
        sql_msg_3 = self.sh_primy.execut_db_sql(sql_cmd_3, dbname='postgres')
        self.logger.info(sql_msg_3)
        self.assertIn('permission denied', sql_msg_3, '执行失败:' + text)

        text = '--step4:普通用户查询dbe_perf.get_global_full_sql_by_timestamp' \
               '函数,expect:失败，合理报错，提示permission denied---'
        self.logger.info(text)
        sql_cmd_4 = f'''set role {self.user_name} password \
                         '{macro.COMMON_PASSWD}';
                        select * from \
                        dbe_perf.get_global_full_sql_by_timestamp\
                        (clock_timestamp() - 1,clock_timestamp());'''
        sql_msg_4 = self.sh_primy.execut_db_sql(sql_cmd_4, dbname='postgres')
        self.logger.info(sql_msg_4)
        self.assertIn('permission denied', sql_msg_4, '执行失败:' + text)

        text = '--step5:普通用户查询dbe_perf.get_global_slow_sql_by_timestamp' \
               '函数,expect:失败，合理报错，提示permission denied---'
        self.logger.info(text)
        sql_cmd_5 = f'''set role {self.user_name} password \
                        '{macro.COMMON_PASSWD}';
                        select * from \
                        dbe_perf.get_global_slow_sql_by_timestamp\
                        (clock_timestamp() - 1,clock_timestamp());'''
        sql_msg_5 = self.sh_primy.execut_db_sql(sql_cmd_5, dbname='postgres')
        self.logger.info(sql_msg_5)
        self.assertIn('permission denied', sql_msg_5, '执行失败:' + text)

        text = '---step6:普通用户查询解析函数,' \
               'expect:失败，合理报错，提示permission denied---'
        self.logger.info(text)
        sql_cmd_6 = f'''set role {self.user_name} \
                    password '{macro.COMMON_PASSWD}';
                    select statement_detail_decode(details,'plaintext',true) \
                    from dbe_perf.get_global_slow_sql_by_timestamp\
                    (clock_timestamp() - 1,clock_timestamp());'''
        sql_msg_6 = self.sh_primy.execut_db_sql(sql_cmd_6, dbname='postgres')
        self.logger.info(sql_msg_6)
        self.assertIn('permission denied', sql_msg_6, '执行失败:' + text)

    def tearDown(self):
        text = '---step7: 环境清理,expect:成功:---'
        self.logger.info(text)
        drop_cmd = f'drop user {self.user_name};'
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG,
                      drop_msg, '执行失败:' + text)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0012 finish---')
